﻿
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Execl数据处理.Models;
using System.Collections.Generic;

namespace Execl数据处理
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //使用OpenXML读取Excel文件
            var filePath = $"{Environment.CurrentDirectory}/Execls/Datas.xlsx";
            //判断当前文件是否存在
            if (!File.Exists(filePath))
            {
                Console.WriteLine($"尚未找到目录文件->>{filePath}");
                return;
            }
            Console.WriteLine($"开始读取Excel文件->>{filePath}");
            var Items = ReadExcel(filePath);
            if (Items.Count == 0)
            {
                Console.WriteLine("读取Excel文件失败，数据为空");
                return;
            }
            Console.WriteLine($"读取Excel文件成功，数据条数：{Items.Count}");
            while (true)
            {
                //输出读取到的数据
                Console.WriteLine("请输入名称");
                var name = Console.ReadLine();
                if (string.IsNullOrEmpty(name)) name = string.Empty;
                var datas = Items.Where(x => x.Name.Contains(name)).ToList();
                //判断是否有数据
                if (datas.Count == 0)
                {
                    Console.WriteLine("未找到相关数据");
                    continue;
                } 
                Console.WriteLine($"找到{datas.Count}条数据");
                foreach (var item in datas)
                {
                    Console.WriteLine($"层级：{item.Layer}, 名称：{item.Name}, 参数大小：{item.ParamSize}");
                }
            }
        }
        //读取Excel文件
        private static List<DataInfo> ReadExcel(string filePath)
        {
            var list = new List<DataInfo>();
            try
            {
                //打开Excel文件
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
                {
                    if (doc == null || doc.WorkbookPart == null || doc.WorkbookPart.WorksheetParts == null) return list;
                    WorkbookPart workbookPart = doc.WorkbookPart; 
                    Sheet sheet = workbookPart.Workbook.Sheets.GetFirstChild<Sheet>(); 
                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                    IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
                    //创建字典将DataInfo里面的属性对应的列索引存起来
                    Dictionary<string, int> keys = new Dictionary<string, int>();
                    foreach (Row row in rows)
                    {
                        var dataInfo = new DataInfo();
                        var cells = row.Elements<Cell>().ToList();
                        if (row.RowIndex == 1)
                        {
                            for (int i = 0; i < cells.Count; i++)
                            {
                                string cellValue = doc.GetCellValue(cells[i]);
                                if (cellValue == nameof(dataInfo.Layer))
                                {
                                    keys.Add(nameof(dataInfo.Layer), i);
                                }
                                else if (cellValue == nameof(dataInfo.Name))
                                {
                                    keys.Add(nameof(dataInfo.Name), i);
                                }
                                else if (cellValue == nameof(dataInfo.ParamSize))
                                {
                                    keys.Add(nameof(dataInfo.ParamSize), i);
                                }
                            }
                            continue;
                        }
                        dataInfo.Layer = doc.GetCellValue(cells[keys[nameof(dataInfo.Layer)]]);
                        dataInfo.Name = doc.GetCellValue(cells[keys[nameof(dataInfo.Name)]]);
                        dataInfo.ParamSize = doc.GetCellValue(cells[keys[nameof(dataInfo.ParamSize)]]);
                        list.Add(dataInfo);
                    }
                }
            }
            catch { }
            return list;
        }
        
    }
    public static class SpreadsheetDocumentExtensions
    {
        public static string GetCellValue(this SpreadsheetDocument doc, Cell cell)
        {
            if (doc == null || doc.WorkbookPart == null || doc.WorkbookPart.SharedStringTablePart == null || cell == null || cell.CellValue == null) return string.Empty;
            SharedStringTablePart stringTablePart = doc.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }
    }
}
